﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.Configuration;

namespace LandandSurvey
{
    public partial class ETL : System.Web.UI.Page
    {
        protected void Page_Load( object sender, EventArgs e )
        {
            if ( Request[ "type" ] == "jn" )
            {
                List<PlanItem> planitems = ReadData();

                gvPreview.DataSource = planitems;
                gvPreview.DataBind();
            }
            else if ( Request[ "type" ] == "1186" )
            {
                List<BookPlan> planitems = ReadData1186();

                gvPreview.DataSource = planitems;
                gvPreview.DataBind();
            }
            else if ( Request[ "type" ] == "Other" )
            {
                List<OtherPlan> planitems = ReadDataOther();

                gvPreview.DataSource = planitems;
                gvPreview.DataBind();
            }
        }

        public List<PlanItem> ReadData()
        {
            try
            {
                // You can change C:\Members.xlsx to any path where 
                // the file is located.
                string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
               Data Source=" + ConfigurationManager.AppSettings[ "DocsFolder" ] + Request[ "id" ] + @";Extended Properties=""Excel 8.0;HDR=NO;""";

                // if you don't want to show the header row (first row)
                // use 'HDR=NO' in the string

                string strSQL = "SELECT * FROM [Sheet1$]";

                OleDbConnection excelConnection = new OleDbConnection( connectionString );
                excelConnection.Open(); // This code will open excel file.

                OleDbCommand dbCommand = new OleDbCommand( strSQL, excelConnection );
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter( dbCommand );

                // create data table
                DataTable dTable = new DataTable();
                dataAdapter.Fill( dTable );

                List<PlanItem> planitems = HydrateObjects( dTable );

                // dispose used objects
                dTable.Dispose();
                dataAdapter.Dispose();
                dbCommand.Dispose();

                excelConnection.Close();
                excelConnection.Dispose();

                return planitems;
            }
            catch ( Exception ex )
            {
                Session[ "app.error" ] = ex.Message;
                Response.Redirect( "Error.aspx" );
                return null;
            }
        }

        public List<PlanItem> HydrateObjects( DataTable dt )
        {
            if ( dt.Rows[ 0 ][ 1 ].ToString() == "OWNER" )
            {
                dt.Rows.RemoveAt( 0 );
            }

            List<PlanItem> planitems = new List<PlanItem>();
            foreach ( DataRow dr in dt.Rows )
            {
                planitems.Add( new PlanItem()
                {
                    PlanReference = dr[ 0 ].ToString(),
                    Owner = dr[ 1 ].ToString(),
                    Location = dr[ 2 ].ToString(),
                    Acreage = dr[ 3 ].ToString(),
                    Surveyor = dr[ 4 ].ToString(),
                    SheetNumber = dr[ 5 ].ToString(),
                    DateSigned = dr[ 6 ].ToString()
                } );
            }

            return planitems;
        }

        public List<BookPlan> ReadData1186()
        {
            // You can change C:\Members.xlsx to any path where 
            // the file is located.
            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
               Data Source=" + ConfigurationManager.AppSettings[ "DocsFolder" ] + Request[ "id" ] + @";Extended Properties=""Excel 8.0;HDR=NO;""";

            // if you don't want to show the header row (first row)
            // use 'HDR=NO' in the string

            string strSQL = "SELECT * FROM [Sheet1$]";

            OleDbConnection excelConnection = new OleDbConnection( connectionString );
            excelConnection.Open(); // This code will open excel file.

            OleDbCommand dbCommand = new OleDbCommand( strSQL, excelConnection );
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter( dbCommand );

            // create data table
            DataTable dTable = new DataTable();
            dataAdapter.Fill( dTable );

            List<BookPlan> planitems = HydrateObjects1186( dTable );

            // dispose used objects
            dTable.Dispose();
            dataAdapter.Dispose();
            dbCommand.Dispose();

            excelConnection.Close();
            excelConnection.Dispose();

            return planitems;
        }

        public List<BookPlan> HydrateObjects1186( DataTable dt )
        {
            if ( dt.Rows[ 0 ][ 1 ].ToString().ToUpper() == "BOOK NO" )
            {
                dt.Rows.RemoveAt( 0 );
            }

            List<BookPlan> planitems = new List<BookPlan>();
            foreach ( DataRow dr in dt.Rows )
            {
                if ( dr[ 0 ].ToString() == string.Empty &&
                    dr[ 1 ].ToString() == string.Empty &&
                    dr[ 2 ].ToString() == string.Empty &&
                    dr[ 3 ].ToString() == string.Empty &&
                    dr[ 4 ].ToString() == string.Empty &&
                    dr[ 5 ].ToString() == string.Empty &&
                    dr[ 6 ].ToString() == string.Empty &&
                    dr[ 7 ].ToString() == string.Empty &&
                    dr[ 8 ].ToString() == string.Empty &&
                    dr[ 9 ].ToString() == string.Empty &&
                    dr[ 10 ].ToString() == string.Empty || dr[ 0 ].ToString().ToLower() == "PLAN REFERENCE".ToLower() )
                {

                }
                else
                {
                    planitems.Add( new BookPlan()
                    {
                        BookNo = dr[ 0 ].ToString(),
                        Folio = dr[ 1 ].ToString(),
                        Name = dr[ 2 ].ToString(),
                        Acreage = dr[ 3 ].ToString(),
                        Ward = dr[ 4 ].ToString(),
                        WardRefCassini = dr[ 5 ].ToString(),
                        WardRefUTM = dr[ 6 ].ToString(),
                        Surveyor = dr[ 7 ].ToString(),
                        DateSignedSurveyor = dr[ 8 ].ToString(),
                        DateSignedDirector = dr[ 9 ].ToString(),
                        SurveyOrderNumber = dr[ 10 ].ToString(),
                        RPOVolume = dr[ 11 ].ToString(),
                        RPOFolio = dr[ 12 ].ToString(),
                        CTVolume = dr[ 13 ].ToString(),
                        CTFolio = dr[ 14 ].ToString(),
                        LandSNumber = dr[ 15 ].ToString(),
                        Remarks = dr[ 16 ].ToString(),
                        DeedNumber = dr[ 17 ].ToString()
                    } );
                }
            }

            return planitems;
        }

        private List<OtherPlan> ReadDataOther()
        {
            // You can change C:\Members.xlsx to any path where 
            // the file is located.
            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
               Data Source=" + ConfigurationManager.AppSettings[ "DocsFolder" ] + Request[ "id" ] + @";Extended Properties=""Excel 8.0;HDR=NO;""";

            // if you don't want to show the header row (first row)
            // use 'HDR=NO' in the string

            string strSQL = "SELECT * FROM [Sheet1$]";

            OleDbConnection excelConnection = new OleDbConnection( connectionString );
            excelConnection.Open(); // This code will open excel file.

            OleDbCommand dbCommand = new OleDbCommand( strSQL, excelConnection );
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter( dbCommand );

            // create data table
            DataTable dTable = new DataTable();
            dataAdapter.Fill( dTable );

            List<OtherPlan> planitems = HydrateObjectsOther( dTable );

            // dispose used objects
            dTable.Dispose();
            dataAdapter.Dispose();
            dbCommand.Dispose();

            excelConnection.Close();
            excelConnection.Dispose();

            return planitems;
        }

        public List<OtherPlan> HydrateObjectsOther( DataTable dt )
        {
            if ( dt.Rows[ 0 ][ 1 ].ToString().ToUpper() == "PLAN REFERENCE" )
            {
                dt.Rows.RemoveAt( 0 );
            }

            List<OtherPlan> planitems = new List<OtherPlan>();
            foreach ( DataRow dr in dt.Rows )
            {
                if ( dr[ 0 ].ToString() == string.Empty &&
                    dr[ 1 ].ToString() == string.Empty &&
                    dr[ 2 ].ToString() == string.Empty &&
                    dr[ 3 ].ToString() == string.Empty &&
                    dr[ 4 ].ToString() == string.Empty &&
                    dr[ 5 ].ToString() == string.Empty &&
                    dr[ 6 ].ToString() == string.Empty &&
                    dr[ 7 ].ToString() == string.Empty &&
                    dr[ 8 ].ToString() == string.Empty &&
                    dr[ 9 ].ToString() == string.Empty &&
                    dr[ 10 ].ToString() == string.Empty || dr[ 0 ].ToString().ToLower() == "PLAN REFERENCE".ToLower() )
                {

                }
                else
                {
                    planitems.Add( new OtherPlan()
                    {
                        PlanReference = dr[ 0 ].ToString(),
                        Name = dr[ 1 ].ToString(),
                        Acreage = dr[ 2 ].ToString(),
                        Ward = dr[ 3 ].ToString(),
                        WardRefCassini = dr[ 4 ].ToString(),
                        WardRefUTM = dr[ 5 ].ToString(),
                        Surveyor = dr[ 6 ].ToString(),
                        DateSignedSurveyor = dr[ 7 ].ToString(),
                        DateSignedDirector = dr[ 8 ].ToString(),
                        SurveyOrderNumber = dr[ 9 ].ToString(),
                        RPOVolume = dr[ 10 ].ToString(),
                        RPOFolio = dr[ 11 ].ToString(),
                        CTVolume = dr[ 12 ].ToString(),
                        CTFolio = dr[ 13 ].ToString(),
                        LandSNumber = dr[ 14 ].ToString(),
                        Remarks = dr[ 15 ].ToString(),
                        DeedNumber = dr[ 16 ].ToString()
                    } );
                }
            }

            return planitems;
        }

        protected void btnBegin_Click( object sender, EventArgs e )
        {
            landsdbEntities container = new landsdbEntities();
            if ( Request[ "type" ] == "jn" )
            {
                List<PlanItem> planitems = ReadData();

                foreach ( PlanItem pi in planitems )
                {
                    if ( pi.Location != string.Empty &&
                        pi.Owner != string.Empty &&
                        pi.PlanReference != string.Empty &&
                        pi.SheetNumber != string.Empty )
                    {
                        var result = ( from i in container.PlanItems
                                       where
                                       i.Location == pi.Location &&
                                       i.Owner == pi.Owner &&
                                       i.PlanReference == pi.PlanReference &&
                                       i.SheetNumber == pi.SheetNumber &&
                                       i.DateSigned == pi.DateSigned &&
                                       i.Surveyor == pi.Surveyor
                                       select i ).ToList();
                        if ( result.Count == 0 )
                        {
                            pi.FileName = Request[ "id" ].ToString();
                            container.PlanItems.AddObject( pi );
                        }
                    }
                }
            }
            else if ( Request[ "type" ] == "1186" )
            {
                List<BookPlan> plansitems = ReadData1186();

                foreach ( BookPlan other in plansitems )
                {
                    var result = ( from i in container.BookPlans
                                   where
                                   i.Acreage == other.Acreage &&
                                   i.CTFolio == other.CTFolio &&
                                   i.CTVolume == other.CTVolume &&
                                   i.DateSignedDirector == other.DateSignedDirector &&
                                   i.DateSignedSurveyor == other.DateSignedSurveyor &&
                                   i.LandSNumber == other.LandSNumber &&
                                   i.Name == other.Name &&
                                   i.Folio == other.Folio &&
                                   i.BookNo == other.BookNo &&
                                   i.Remarks == other.Remarks &&
                                   i.RPOFolio == other.RPOFolio &&
                                   i.RPOVolume == other.RPOVolume &&
                                   i.Surveyor == other.Surveyor &&
                                   i.SurveyOrderNumber == other.SurveyOrderNumber &&
                                   i.Ward == other.Ward &&
                                   i.WardRefCassini == other.WardRefCassini &&
                                   i.WardRefUTM == other.WardRefUTM
                                   select i ).ToList();

                    if ( result.Count == 0 )
                    {
                        other.FileName = Request[ "id" ].ToString();
                        container.BookPlans.AddObject( other );
                    }
                }
            }
            else if ( Request[ "type" ] == "Other" )
            {
                List<OtherPlan> plansitems = ReadDataOther();

                foreach ( OtherPlan other in plansitems )
                {
                    var result = ( from i in container.OtherPlans
                                   where
                                   i.Acreage == other.Acreage &&
                                   i.CTFolio == other.CTFolio &&
                                   i.CTVolume == other.CTVolume &&
                                   i.DateSignedDirector == other.DateSignedDirector &&
                                   i.DateSignedSurveyor == other.DateSignedSurveyor &&
                                   i.LandSNumber == other.LandSNumber &&
                                   i.Name == other.Name &&
                                   i.PlanReference == other.PlanReference &&
                                   i.Remarks == other.Remarks &&
                                   i.RPOFolio == other.RPOFolio &&
                                   i.RPOVolume == other.RPOVolume &&
                                   i.Surveyor == other.Surveyor &&
                                   i.SurveyOrderNumber == other.SurveyOrderNumber &&
                                   i.Ward == other.Ward &&
                                   i.WardRefCassini == other.WardRefCassini &&
                                   i.WardRefUTM == other.WardRefUTM
                                   select i ).ToList();

                    if ( result.Count == 0 )
                    {
                        other.FileName = Request[ "id" ].ToString();
                        container.OtherPlans.AddObject( other );
                    }
                }
            }
            container.SaveChanges();
            Response.Redirect( "SuccessETL.aspx" );
        }
    }
}